With each version of SQL Server, there are always a few new features introduced that we applaud as we finally have access to a useful function that is already available elsewhere.

Introduced in SQL Server 2025 CTP 1.3, the PRODUCT() function acts similarly to SUM(), but multiplies values rather than adds them. It is an aggregate function in SQL Server and therefore operates on a data set, rather than on scalar values.

Calculating a Product Without PRODUCT()

Prior to the existence of this function, writing T-SQL to multiply a series of set-based values was possible, though not exactly pretty. Consider a scenario where there is a need to multiply a set of values over time to calculate an ever-increasing multiplicative metric, such as interest or inflation.

Consider the following data set that models a bank account where the interest rate varies over time:

The data includes an annual interest rate that is updated monthly, which is common for most bank accounts. Calculating the end balance requires dividing the annual interest rate by twelve to get the monthly interest rate, adding one, and multiplying by the balance. For example, for January 2024 (rounded to the nearest hundredth):

$100.00 * (1 + (0.0523 / 12.0000)) = $100.00 * (1 + 0.004358) = 100 * 1.004358 = 100.44

A useful calculation would be to determine the overall interest rate for any period of time. The most accurate way to do this would be to multiply the interest rates over the time span that is to be analyzed. For example, consider calculating the overall annual interest rate for 2024. To do this would require multiplying all twelve monthly interest rates together from 2024. There is no function to do this, though. That leaves us with two options: The iterative approach and the head-scratching math approach.

This is a simple example of how to iterate through the table and get the annual interest rate for 2024:

The result is as follows:

While we get the correct answer, iteration is not an ideal solution. For a large table with thousands or millions of rows, this will quickly become slow and inefficient. When working with set-based data, the ideal solution will be set-based.

The set-based method of solving this problem involves some mathematical manipulation. Compute the log (base ten) for each value, sum the results, and then raise the result to the power of 10. That calculation takes advantage of the Product Rule, which is a mathematical property of logarithms. It is defined as:

                                                           Log (X * Y) = Log X + Log Y

Therefore, the annual interest rate for 2024 can be calculated like this:

The result returned is:

The result is slightly different as the iterative approach rounded the result after each iteration, whereas the set-based approach rounds after all other calculations are complete. The same calculation can be made using this query, as well:

The only difference is that the result is not rounded:

While these calculations are far more efficient, it is also quite confusing. Anyone reading this code will not know why logarithms and powers are used here. Even with some added documentation, most software developers will not be familiar with the mathematical properties of logarithms, nor would they be expected to.

Simplifying Code With PRODUCT()

SQL Server 2025 provides the long-awaited solution to this problem: The PRODUCT() function. This function behaves exactly like aggregate function SUM(), except that it multiplies values rather than adding them.

Here is the solution to the previous problem, now using PRODUCT():

The result is also a bit different, due to slightly different rounding:

Of all the syntax options provided so far, this is by far the simplest, easiest to read, and most efficient. The PRODUCT() function turns longer and more complex code into a single easy-to-read function call. This function is optimized for batch mode operations and will result in significant performance gains when batch mode is used, regardless of whether on columnstore or rowstore indexes.

Details of PRODUCT()

The PRODUCT() function has some details that are worth noting here that can affect how we use it or the results that are returned. These are some of the more common questions that could be asked about it.

INT vs. BIGINT Results

First, this function returns an INTEGER data type. This is important as multiplying many numbers together can quickly result in a very large number. If the number exceeds the threshold of an integer in either the positive or negative direction, then an error will be returned. The limits for each integer data type are provided here, for reference.

While 2.1 billion may seem like a big number, it is quite easy to exceed it when talking about world population, data size, money, or other biggie-sized data points. A simple example of what happens when the result exceeds this limit is as follows:

That query multiplies a larger set of numbers and ultimately breaks the INTEGER limit. The result is an error message:

Since there is no BIGINT version of PRODUCT() (yet), it is important to be aware of data size and ensure that results can’t throw an error like above. COUNT() is the only aggregate function that currently has a BIGINT version available, COUNT_BIG(). Therefore, we should treat PRODUCT() with the same caution that we would treat SUM().

NULL Handling

NULL is handled the same way as it is for other aggregate functions. This can be easily tested by adding NULL to the test data set from above:

Calculating the annual interest rate for 2025 can be done with this (slightly adjusted) query:

The result is calculated using the values for January-March:

NULL is ignored in the calculation and only the previously populated values are taken into consideration.

PRODUCT() As a Window Function

The PRODUCT() function can be used as part of a window function to compute a product across a non-aggregated data set. This can be a useful way to add in more complex metrics, such as a running multiplier.

The following example calculates a running interest rate per calendar year:

The results show a running multiplier for interest rate that resets at the start of a new year:

Note that when the interest rate is NULL for 4/1/2025-6/1/2025, the running total is still correctly reported as PRODUCT ignores NULL and simply returns the running total up through the actual values provided previously.

A similar query can be crafted to return a running total by quarter:

As expected, the interest rate accumulates for each of the three months per quarter and resets when the next quarter starts. 2025 Quarter 2 only shows NULL as all interest rates are reported as NULL. If PRODUCT() receives no non-NULL values, then NULL is returned.

Conclusion

PRODUCT() provides functionality that will make lives easier for many data engineers and analysts. Being able to calculate a running product quickly and efficiently with simple syntax is long-awaited functionality for SQL Server!

This is especially useful for percentages and rates, where long-term calculations can become cumbersome and inefficient. Take note of large numbers, though, as PRODUCT() is only designed to return an INTEGER. Results that cross into BIGINT territory will throw an error instead of the expected result.